package jdmx.library.da;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import jdmx.library.info.DbInfo;



import org.apache.commons.dbcp.BasicDataSource;

public abstract class Database {
	protected BasicDataSource dataSource;
	
	public void init(DbInfo dbInfo) {
		// to be overridden
	}
	
	public DataTable fetch(SqlStatement sqlStatement) throws SQLException 
	{
		Connection conn = null;
        ResultSet rs = null;
        PreparedStatement stmt = null;

        DataTable dataTable = new DataTable();
        String normalizedSql = SqHelper.replaceParamsIntoSql(sqlStatement.text);
		ArrayList<String> paramNames = SqHelper.getParamsFromSql(sqlStatement.text);
        ArrayList<Object> paramValues = new ArrayList<Object>();        
        for (String paramName : paramNames) {
			Object paramValue = null;
	    	for (SqlParam dbParam : sqlStatement.paramList) {
	    		if (dbParam.name.equalsIgnoreCase(paramName)) {
	    			paramValue = dbParam.value;
	    			break;
	    		}
			}
	    	if (paramValue == null) {
	    		paramValue = "%";
	    	}
	    	paramValues.add(paramValue);
		}
        
    	try {
			conn = dataSource.getConnection();
			stmt = conn.prepareStatement(normalizedSql);
		    for (int i = 0; i < paramValues.size(); i++) {
			    stmt.setObject(i+1, paramValues.get(i));
		    }
		    rs = stmt.executeQuery();
	        ResultSetMetaData rsmd = rs.getMetaData();
	        int columnCount = rsmd.getColumnCount();
			for (int i = 0; i < columnCount; i++) {
				dataTable.addColumn(rsmd.getColumnLabel(i+1), "") ;
			}
			while (rs.next()) {
				DataRow dataRow = dataTable.addRow();
				for (DataColumn column : dataTable.columns) {
					dataRow.setCell(column.name, rs.getObject(column.name));
				}
			}
		} finally { 
			try { if (rs != null) rs.close(); 
			} finally { 
				try { if (stmt != null) stmt.close(); } 
				finally { if (conn != null) conn.close(); } 
			} 
		}
        
    	return dataTable;
    }

	public Integer execute(SqlStatement sqlStatement) throws SQLException 
	{
		Connection conn = null;
        ResultSet rs = null;
        PreparedStatement stmt = null;

        Integer result = null;
        String normalizedSql = SqHelper.replaceParamsIntoSql(sqlStatement.text);
		ArrayList<String> paramNames = SqHelper.getParamsFromSql(sqlStatement.text);
        ArrayList<Object> paramValues = new ArrayList<Object>();        
        for (String paramName : paramNames) {
			Object paramValue = null;
	    	for (SqlParam dbParam : sqlStatement.paramList) {
	    		if (dbParam.name.equalsIgnoreCase(paramName)) {
	    			paramValue = dbParam.value;
	    			break;
	    		}
			}
	    	if (paramValue == null) {
	    		paramValue = "%";
	    	}
	    	paramValues.add(paramValue);
		}
        
    	try {
			conn = dataSource.getConnection();
    	    stmt = conn.prepareStatement(normalizedSql, Statement.RETURN_GENERATED_KEYS);
		    for (int i = 0; i < paramValues.size(); i++) {
			    stmt.setObject(i+1, paramValues.get(i));
		    }
		    stmt.executeUpdate();
    	    rs = stmt.getGeneratedKeys();
            if (rs.next()) 
            	result = rs.getInt(1);
		} finally { 
			try { if (rs != null) rs.close(); 
			} finally { 
				try { if (stmt != null) stmt.close(); } 
				finally { if (conn != null) conn.close(); } 
			} 
		}
        
    	return result;
    }
}

           
       